package com.mine.site.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.mine.site.model.ShoppingCart;
import com.mine.site.util.DbUtil;
public class ShoppingCartDao {

	public List<ShoppingCart> findAll() {
		List<ShoppingCart> cart = new ArrayList<ShoppingCart>();
		String sql = "select * from shoppingcart";
		Statement stmt = null;
		try {
			stmt = DbUtil.getInstance().getConn().createStatement();
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				ShoppingCart u = new ShoppingCart();
				u.setId(rs.getString(1));
				u.setName(rs.getString(2));
				u.setNumber(rs.getString(3));
				u.setTitle(rs.getString(4));
				u.setPrice(rs.getString(5));
				u.setUrl(rs.getString(6));
				cart.add(u);
			}
			rs.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return cart;
	}

	public List<ShoppingCart> findByName(String Name){
		List<ShoppingCart> cart = new ArrayList<ShoppingCart>();
			 String sql = "select * from shoppingcart where name = ?";
			 ShoppingCart u =null;
				try {
					PreparedStatement pst = DbUtil.getInstance().getConn().prepareStatement(sql);
					pst.setString(1, Name);
					ResultSet rs = pst.executeQuery();
					if (rs.next()) {
						u = new ShoppingCart();
						u.setId(rs.getString(1));
						u.setName(rs.getString(2));
						u.setNumber(rs.getString(3));
						u.setTitle(rs.getString(4));
						u.setPrice(rs.getString(5));
						u.setUrl(rs.getString(6));
					
					}
					rs.close();
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return cart;
		 }
	
	  public void delete(String Url){
			String sql = "delete from shoppingcart where url=?";
			PreparedStatement pst = null;
			try {
				pst = DbUtil.getInstance().getConn().prepareStatement(sql);
				pst.setString(1, Url);
				
				pst.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	  
	  public void delete1(){
			String sql = "delete from shoppingcart";
			PreparedStatement pst = null;
			try {
				pst = DbUtil.getInstance().getConn().prepareStatement(sql);				
				pst.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
	  
	  public void save(ShoppingCart u){
			String sql="insert into shoppingcart values(?,?,?,?,?,?)";
			PreparedStatement pst = null;
			try {
				pst = DbUtil.getInstance().getConn().prepareStatement(sql);
				pst.setString(1, u.getId());
				pst.setString(2, u.getName());
				pst.setString(3, u.getNumber());
				pst.setString(4, u.getTitle());
				pst.setString(5, u.getPrice());
				pst.setString(6, u.getUrl());
				
				pst.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}finally{
				try {
					pst.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}
		}
}
